package cc.messcat.dao.system;

import cc.messcat.common.dao.BaseHibernateDaoUtil;
import cc.messcat.common.model.JDBCSettingBean;
import cc.messcat.entity.fupin.Family;
import cc.messcat.entity.fupin.PoorFamily;
import cc.messcat.entity.fupin.PoorVillage;
import cc.messcat.entity.log.Log;
import cc.messcat.entity.system.McUser;
import cc.messcat.service.log.LogService;
import cc.messcat.util.Collections;
import org.apache.commons.io.FileUtils;
import org.apache.commons.lang.StringUtils;
import org.hibernate.jdbc.Work;
import org.hibernate.transform.Transformers;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import javax.persistence.*;
import javax.servlet.http.HttpSession;
import java.io.*;
import java.lang.reflect.Field;
import java.math.BigInteger;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.*;

@Repository
public class SystemDaoImpl extends BaseHibernateDaoUtil implements SystemDao {

	final static Logger log = LoggerFactory.getLogger("年度数据备份");
	
	private final static String BACKUP_SUCCESS = "备份成功";
	private final static String BACKUP_DUPLICATE = "重复备份";
	
	@Autowired
	private LogService logService;
	
	@Autowired
	private JDBCSettingBean jdbcSettingBean;
	
	
	private String historyFilename =  "backup.history";
	private String errLog = "err.log";
	private String separator = "*************************************";
	
	/**
	 * 年度数据备份
	 * @throws Exception 
	 */
	@Override
	@SuppressWarnings("all")
	public Map<String, Object> annualBackup(Integer origin, Integer derivation,HttpSession session,String ip) throws Exception {
		Map backupProgress = (Map) session.getAttribute("backupProgress");
		Map<String, Object> result = new HashMap<>();
		//备份文件的文件名
		String filename = new SimpleDateFormat("yyyy-MM-dd_HHmmss").format(new Date());

		String info = "备份开始（源数据年度："+ origin +" | 目标年度："+ derivation +" | 开始时间："+ new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date())+"）";
		backupProgress.put("progress", "1%");
		backupProgress.put("info",info);

		log.info(info);
		Long start = System.currentTimeMillis();
		
		//恢复文件
		try {
			backupProgress.put("progress", "5%");
			backupProgress.put("info","准备恢复文件");
			log.info("准备恢复文件");

			//完整备份文件
			generatePermanentFile(session,filename);
			//临时文件，如果备份失败则删除
			generateRestoreFile(session,filename+".tmp");

			backupProgress.put("progress", "10%");
			backupProgress.put("info","更新数据库对象");
			log.info("更新数据库对象");

		} catch (Exception e) {
			backupProgress.put("info","准备恢复文件失败");
			log.error("准备恢复文件失败");
			e.printStackTrace();
			throw new RuntimeException(e);
		}
		// 村数据表备份开始
		backupProgress.put("progress", "15%");
		backupProgress.put("info","贫困村数据备份开始");
		log.info("贫困村数据备份开始");
		String table = "poor_village";
		//贫困村表备份结果
		Map<String, Object> poorVillageResult = null;
		//新旧数据主键映射
		Map<Long, Long> pvPrincipalKeyAss = null;
		//受影响的中间表记录
		final Map<Long,Long> hupvTemp = new HashMap<>();
		try {
			final String poorVillageField =
					"poor_village.ADD_TIME,poor_village.ASPHALT,poor_village.BROAD_BAND,poor_village.BUS,poor_village.`CODE`,"
					+ "poor_village.COLLECTIVE,poor_village.CONTACT,poor_village.COOPERATIVE,poor_village.CRY,poor_village.CUED_LAND,"
					+ "poor_village.CTY,poor_village.CULTURE,poor_village.DANGER,poor_village.DEVELOPMENT,poor_village.DIFFICULTY,"
					+ "poor_village.DOCTOR,poor_village.ELECTRIC,poor_village.FAN_TIME,poor_village.FARM,poor_village.FARM_INCOME,poor_village.FARM_LAND,"
					+ "poor_village.FARMER,poor_village.FIVE_HOUSE,poor_village.FIVE_NUMBER,poor_village.FORA_GEGRASS,poor_village.FPEOPLE,poor_village.FRUIT,"
					+ "poor_village.GARBAGE,poor_village.GOOUT_NUMBER,poor_village.GROUP_NUMBER,poor_village.HELP_PERUNIT,poor_village.HYGIENE,poor_village.INTERNET,"
					+ "poor_village.IRRIGATION,poor_village.IS_DEL,poor_village.JOB,poor_village.LABOUR_NUMBER,poor_village.LESS_NUMBER,"
					+ "poor_village.LIFA,poor_village.LIFE_NUMBER,poor_village.LOW_HOUSE,poor_village.LOW_NUMBER,poor_village.MAJOR,poor_village.MEDICAL,"
					+ "poor_village.`NAME`,poor_village.NATL_NUMBER,poor_village.NATURALS,poor_village.NATURE,poor_village.OFFICER,poor_village.OPEN_NUMBER,"
					+ "poor_village.PARTY,poor_village.PERSONNEL,poor_village.POST,poor_village.REMNANT_NUMBER,poor_village.RURAL_PENSION,poor_village.SCHOOL,"
					+ "poor_village.`SECURITY`,poor_village.Terrain,poor_village.TOILET,poor_village.TOTAL,poor_village.TOTAL_NUMBER,poor_village.TOURISM,"
					+ "poor_village.TOWN_PENSION,poor_village.TUO_TIME,poor_village.TYPE,poor_village.UP_TIME,poor_village.VILLAGES,poor_village.WATERS,"
					+ "poor_village.WOMAN_NUMBER,poor_village.WOOD_LAND,poor_village.BROADBANDS,poor_village.CITY,poor_village.DISTANCE,poor_village.ELECTRICS,"
					+ "poor_village.LIFES,poor_village.POOR,poor_village.POOR_NUMBER,poor_village.PROVINCE,poor_village.ROAD,poor_village.VILLAGE,"
					+ "poor_village.AREA_ID,poor_village.`STATUS`,";

		poorVillageResult = tableBackup(origin, derivation, PoorVillage.class,table, poorVillageField,null);
		result.put("poorVillage", poorVillageResult.get("result"));
		pvPrincipalKeyAss = (Map<Long, Long>) poorVillageResult.get("associate");
		if(pvPrincipalKeyAss == null) log.warn("已备份贫困村数据记录条数为0");
		//村数据备份结束
		
		//帮扶单位_贫困村中间表维护开始
		if(pvPrincipalKeyAss != null) {
			log.warn("已备份贫困村数据记录条数:"+pvPrincipalKeyAss.size());
			//进度更新
			backupProgress.put("progress", "20%");

			final List<Map> hupv = getCurrentSession().createSQLQuery(" select PoorVillageId as pvId ,helpUnitsId as huId from helpunits_and_poorvillage")
				.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
			final Map<Long,Long> hupvAss = new HashMap<>(hupv.size()+1,1F);
			
			for(Map e : hupv) {
				hupvAss.put(Long.parseLong(e.get("pvId").toString()),Long.parseLong(e.get("huId").toString()));
			}
			//筛选受影响记录
			for (Map.Entry<Long,Long> entry : pvPrincipalKeyAss.entrySet()) {
				if(hupvAss.containsKey(entry.getKey())) {
					hupvTemp.put(hupvAss.get(entry.getKey()), entry.getValue());
				}
			}
			backupProgress.put("progress", "25%");
			log.warn("帮扶单位_贫困村中间表总记录数为：" + hupv.size() + "， 本次备份受影响记录条数："+hupvTemp.size()+"，已更新");
			//更新中间表受影响记录
			getCurrentSession().doWork(new Work() {
				@Override
				public void execute(Connection con) throws SQLException {
					String hupvBackup = " insert into helpunits_and_poorvillage(helpUnitsId,PoorVillageId) values(?,?)";
					PreparedStatement ps = con.prepareStatement(hupvBackup);
					for (Map.Entry e : hupvTemp.entrySet()) {
						ps.setObject(1, e.getKey());
						ps.setObject(2, e.getValue());
						ps.addBatch();
					}
					ps.executeBatch();
				}
			});
		}
		//帮扶单位_贫困村中间表维护结束
		updateANNUAL(table, origin);
		log.info(poorVillageResult.get("result")+"-贫困村数据备份结束，耗时："+ (System.currentTimeMillis()-start)+"ms");
		//进度更新
		backupProgress.put("progress", "30%");
		backupProgress.put("info",poorVillageResult.get("result")+"-贫困村数据备份结束，耗时："+ (System.currentTimeMillis()-start)+"ms");
		
		addLog(ip,session, "poor_village", origin, derivation);

		//失败时数据的回滚操作
		} catch (Exception e1) {
			e1.printStackTrace();
			log.error("贫困村数据备份失败，正在回滚操作");
			//进度更新
			backupProgress.put("info","贫困村数据备份失败，正在回滚操作");
			try {
				if(hupvTemp != null ) {
					log.info("回滚帮扶单位_贫困村中间表备份操作");
					getCurrentSession().doWork(new Work(){
						@Override
						public void execute(Connection con) throws SQLException {
							String rollback = " delete from helpunits_and_poorvillage where PoorVillageId=?";
							PreparedStatement ps = con.prepareStatement(rollback);
							for (Map.Entry<Long,Long> e : hupvTemp.entrySet()) {
								ps.setObject(1, e.getValue());
								ps.addBatch();
							}
							ps.executeBatch();
						}
					});
				}
				log.info("回滚贫困村表备份操作");
				getCurrentSession().createSQLQuery(" update "+ table +" set annual='"+origin+"' where annual='"+ derivation +"'").executeUpdate();
				getCurrentSession().createSQLQuery(" delete from "+ table +" where annual like 'derive%'").executeUpdate();
				
				info = "贫困村数据备份操作回滚成功，耗时"+(System.currentTimeMillis()-start) + "ms";
				log.info(info);
				backupProgress.put("info",info);
				result.put("poorVillage","贫困村备份失败，操作已撤销，数据回滚成功");
			} catch (Exception e) {
				info = "贫困村数据备份失败，回滚操作失败，耗时"+(System.currentTimeMillis()-start) + "ms";
				log.error(info);
				backupProgress.put("info",info);
				result.put("poorVillage","贫困村备份失败，操作已撤销，数据回滚失败");
				//删除临时文件
				finish(filename,true);
				e.printStackTrace();
				throw new RuntimeException(e);
			}
		}
		

		//贫困户表备份开始
		info = "贫困户数据备份开始";
		log.info(info);
		backupProgress.put("progress","40%");
		backupProgress.put("info","贫困户数据备份中");
		table = "poor_family";
		//受影响的中间表记录
		final Map<Long,Long> hppfTemp = new HashMap<>();
		
		Map<String,Object> poorFamilyResult =  null;
		Map<String,Object> familyResult = null;
			
		try {
			String poorFamilyField =
					"ADD_TIME,ALONE,ATTRIBUTE,BANK,CARD_NUMBER,CITY,COMPANY,CONTACT," +
					" COOPERATIVE,COUNTRY,COUNTY,CUED_LAND,DANGER,DIFFICULTY,DISTANCE,ECOLOGY,ELSES,EXPENSES," +
					" FARM_LAND,FIVE,FORA_GEGRASS,FRUIT,FUEL,HOUSING,INCOME,IRRIGATION,IS_DEL,`LEVEL`,LIFE," +
					" LOW,NUMBER,OPERATING,OTHER,PENSION,PERCAPITA,PLANNING,POVERTY,PRODUCTION,PROPERTY,PEROVINCE," +
					" REASON,ROOM,`SECURITY`,SOLDIER,STANDARD,TOILET,TRANSFER,TWOGIRL,TYPE,UP_TIME,VEHICLE,VILLAGE," +
					" WAGES,WATERS,WOOD_LAND,`YEAR`,AREA_ID,HELP_PERSON_ID,AREACODE,AREACODES,`NAME`,EMPLOYMENT,migrate," +
					" PREVENT_INSURANCE,PREVENT_REASON,BREED,BREED_NAME,BREED_NICKNAME,BREED_SALES,BREED_SUBSIDY_AMO,CAP_AMO," +
					" CAP_BOMAMO,CAP_ENTER,CAP_NAME,EQU_AMO,EQU_ENTER,EQU_NAME,EQU_SBNAME,FA_INCOME,FA_NICKNAME,FA_SALES," +
					" FA_MACHIN_NAME,FA_MILY_WORK,FP_RELIEF,LOAN,LOAN_MD,LOAN_PEOPLE,LOAN_TERM,LOAN_AMO,MIF_EMPNAME,MIF_SALARY," +
					" MIFACTORY,MIF_FACTORY_NAME,PHO_ANNINC,PHO_NICKNAME,PHO_STOCK,PLAN_NAME,PLAN_NICKNAME,PLAN_SALES," +
					" PLAN_SUBSIDY_AMO,PLANTING,RELIEF_AMO,RELIEF_OBJ,SHARES,capture_income,foodstuff_allowance," +
					" other_transfer,solatium,APPLICATION_TIME,CALAMITY,DISTRIBUTE_IMG,EDUCATION,EVALUATION_IMG," +
					" EVALUATION_TIME,ILLNESS,PREVENT_ATTRIBUTE,PUBLICATION_IMG,PUBLICATION_TIME,RESULT_CONTENT," +
					" RESULT_TIME,REVIEW_CONCLUSION,REVIEW_TIME,VERIFIER,APPLICATION_TIME_IMG,CALAMITY_IMG,EDUCATION_IMG," +
					" ILLNESS_IMG,RELIEF_AMO_IMG,RELIEF_OBJ_IMG,RESULT_CONTENT_IMG,";

			poorFamilyResult = tableBackup(origin, derivation,PoorFamily.class, table, poorFamilyField,null);
			result.put("poorFamily",poorFamilyResult.get("result"));
			//新旧数据主键映射
			final Map<Long, Long> pfPrincipalKeyAss = (Map<Long, Long>) poorFamilyResult.get("associate");
			if(pfPrincipalKeyAss == null) log.warn("已备份贫困户数据记录条数为0");
			
			
			//家庭成员表备份开始
			info = "家庭成员表备份开始";
			log.info(info);
			backupProgress.put("progress","50%");
			backupProgress.put("info","家庭成员表备份中");
			
			String familyField = "family.ADD_TIME,family.BIRTH_TIME,family.CITY,family.COMPANY,family.COUNTY,family.CULTURE,family.DE_TIME,"
				+ "family.DO_TYPE,family.ENTERTIME,family.HEALTHY,family.ID_NUMBER,family.ID_TYPE,family.INSTITUTION,family.IS_DEL,family.IS_STUDENT,"
				+ "family.LABOUR,family.LARGE,family.LOW,family.MAJOR,family.`NAME`,family.NATION,family.NUMBER,family.POLITICAL,family.PROVINCE,"
				+ "family.RELATION,family.SCHOOL,family.SCHOOLNAME,family.SEX,family.SOLDIER,family.TELEPHONE,family.UP_TIME,family.VILLAGE,"
				+ "family.WORK_TIME,family.PID,family.STATE,family.FID,";

			familyResult = tableBackup(origin, derivation,Family.class, "family" , familyField,null);
			result.put("family",familyResult.get("result"));
			final Map<Long,Long> fPrincipalKeyAss = (Map<Long, Long>) familyResult.get("associate");	
			if(fPrincipalKeyAss == null) log.warn("已备份家庭成员数据记录条数为0");
			backupProgress.put("progress","60%");

			//更新外键
			if(pfPrincipalKeyAss != null && fPrincipalKeyAss != null) {
				log.info("已备份家庭成员数据记录数：" + fPrincipalKeyAss.size());
				getCurrentSession().doWork(new Work() {
					@Override
					public void execute(Connection con) throws SQLException {
						String updateForeignKey = " update family set FID=? where IS_DEL='0' and FID=? and ANNUAL LIKE 'derive%'";
						PreparedStatement ps = con.prepareStatement(updateForeignKey);
						for(Map.Entry<Long, Long> e : pfPrincipalKeyAss.entrySet()) {
							ps.setObject(1,e.getValue());
							ps.setObject(2,e.getKey());
							ps.addBatch();
						}
						ps.executeBatch();
					}
				});
			}
			backupProgress.put("progress","70%");
			//家庭成员表备份结束
			
			//帮扶人和贫困户中间表
			if(pfPrincipalKeyAss != null) {
				log.warn("已备份贫困户数据记录条数:"+pfPrincipalKeyAss.size());
				final List<Map> hppf = getCurrentSession().createSQLQuery(" select poorFamilyId as pfId ,helpPersonId as hpId from HELP_PERSON_AND_POOR_FAMILY")
					.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
				final Map<Long,Long> hppfAss = new HashMap<>(hppf.size()+1,1F);
				for(Map e : hppf) {
					hppfAss.put(Long.parseLong(e.get("pfId").toString()),Long.parseLong(e.get("hpId").toString()));
				}
				
				//筛选受影响记录
				for (Map.Entry<Long,Long> entry : pfPrincipalKeyAss.entrySet()) {
					if(hppfAss.containsKey(entry.getKey())) {
						hppfTemp.put(hppfAss.get(entry.getKey()), entry.getValue());
					}
				}
				backupProgress.put("progress","80%");
				log.warn("帮扶人_贫困户中间表总记录数为：" + hppf.size() + "， 本次备份受影响记录条数："+hppfTemp.size()+"，已更新");
				//更新中间表受影响记录
				getCurrentSession().doWork(new Work() {
					@Override
					public void execute(Connection con) throws SQLException {
						String hupvBackup = " insert into HELP_PERSON_AND_POOR_FAMILY(helpPersonId,poorFamilyId) values(?,?)";
						PreparedStatement ps = con.prepareStatement(hupvBackup);
						for (Map.Entry e : hppfTemp.entrySet()) {
							ps.setObject(1, e.getKey());
							ps.setObject(2, e.getValue());
							ps.addBatch();
						}
						ps.executeBatch();
					}
				});
			}	
			
			//帮扶记录维护开始
			if(pfPrincipalKeyAss != null) {
				log.info("帮扶记录维护开始");
				backupProgress.put("progress","90%");
				backupProgress.put("info","帮扶记录维护开始");
				
				List<Map<String,BigInteger>> visitRecds = getCurrentSession().createSQLQuery(" select id as id ,pid as pid from visit_recd where is_del='0'  and pid is not null")
					.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
				
				//筛选受影响的帮扶走访记录
				final Map<Long,Long> affectedVisitRecdPID = new HashMap<>();
				for(Map<String,BigInteger> e :visitRecds) {
					Long newPoorFamilyId = pfPrincipalKeyAss.get(((BigInteger)e.get("pid")).longValue());
					if(newPoorFamilyId != null) {
						affectedVisitRecdPID.put(((BigInteger)e.get("pid")).longValue(), newPoorFamilyId);
					}
				}
				
				//备份并更新记录的PID,FID字段
				getCurrentSession().doWork(new Work() {
					@Override
					public void execute(Connection con) throws SQLException {
						String visitRecordBackup = " insert into visit_recd(visit_recd.ACCTOTAL,visit_recd.ACCESSORY_Url,visit_recd.ADD_TIME,"
							+ " visit_recd.CONTENT,visit_recd.IS_DEL,visit_recd.REMARKS,"
							+ " visit_recd.TARGET,visit_recd.UP_TIME,visit_recd.ADDTOR,visit_recd.EDITTOR,visit_recd.PID)"
							+ " select visit_recd.ACCTOTAL,visit_recd.ACCESSORY_Url,visit_recd.ADD_TIME,visit_recd.CONTENT, 3 as is_del,"
							+ " visit_recd.REMARKS,visit_recd.TARGET,visit_recd.UP_TIME,visit_recd.ADDTOR,visit_recd.EDITTOR,? as pid"
							+ " from visit_recd where is_del='0' and pid=?";
						PreparedStatement ps = con.prepareStatement(visitRecordBackup);
						for (Map.Entry e : affectedVisitRecdPID.entrySet()) {
							ps.setObject(1, e.getValue());
							ps.setObject(2, e.getKey());
							ps.addBatch();
						}
						ps.executeBatch();
					}
				});
			}
			//走访记录维护结束
			
			//贫困户表备份结束

			backupProgress.put("progress","95%");
			backupProgress.put("info","正在更新备份数据");
			//所有备份成功，更新年度或临时标记
			updateANNUAL(table, origin);
			updateANNUAL("family", origin);
			getCurrentSession().createQuery("update VisitRecd set isDel=0 where isDel=3").executeUpdate();
			
			log.info(poorFamilyResult.get("result")+"-贫困户数据备份结束，耗时："+ (System.currentTimeMillis()-start)+"ms");

			addLog(ip,session, "poor_family", origin, derivation);
			addLog(ip,session, "family", origin, derivation);
			//失败后的数据回滚
		} catch (Exception e) {
			
			e.printStackTrace();
			info = "贫困户数据备份失败，正在回滚操作";
			log.error(info);
			backupProgress.put("info",info);

			try {
				if(hppfTemp != null ) {
					log.info("回滚帮扶人_贫困户中间表备份操作");
					getCurrentSession().doWork(new Work(){
						@Override
						public void execute(Connection con) throws SQLException {
							String rollback = " delete from  HELP_PERSON_AND_POOR_FAMILY where poorFamilyId=?";
							PreparedStatement ps = con.prepareStatement(rollback);
							for (Map.Entry<Long,Long> e : hppfTemp.entrySet()) {
								ps.setObject(1, e.getValue());
								ps.addBatch();
							}
							ps.executeBatch();
						}
					});
				}
				log.info("回滚贫困户表备份操作");
				getCurrentSession().createSQLQuery(" update "+ table +" set annual='"+origin+"' where annual='"+ derivation +"'").executeUpdate();
				getCurrentSession().createSQLQuery(" delete from "+ table +" where annual like 'derive%'").executeUpdate();
				
				log.info("回滚家庭成员备份操作");
				getCurrentSession().createSQLQuery(" update family set annual='"+origin+"' where annual='"+ derivation +"'").executeUpdate();
				getCurrentSession().createSQLQuery(" delete from family where annual like 'derive%'").executeUpdate();
				
				log.info("回滚走访记录备份操作");
				getCurrentSession().createSQLQuery(" delete from visit_recd where is_del=3 ").executeUpdate();
				
				info = "贫困户数据备份操作回滚成功，耗时"+(System.currentTimeMillis()-start) + "ms";
				log.info(info);
				result.put("poorFamily","贫困户备份失败，操作已撤销，数据回滚成功");
				result.put("family","家庭成员备份失败，操作已撤销，数据回滚成功");
				backupProgress.put("info",info);
			} catch (Exception e1) {
				info = "贫困户数据备份失败，回滚操作失败，耗时"+(System.currentTimeMillis()-start) + "ms";
				result.put("poorFamily","贫困户备份失败，操作已撤销，数据回滚失败");
				result.put("family","家庭成员备份失败，操作已撤销，数据回滚失败");
				backupProgress.put("info",info);
				log.error(info);
				//删除临时文件
				finish(filename,true);
				e1.printStackTrace();
				throw new RuntimeException(e1);
			}
		} 
		//end
		
		Long spend = System.currentTimeMillis()-start;
		Map poorVillageCount = (Map)poorVillageResult.get("associate");
		Map poorFamilyCount = (Map)poorFamilyResult.get("associate");
		Map familyCount = (Map)familyResult.get("associate");
		generateHistoryFile(Collections.generateHashMap(
			new String[]{"effect","spend","poor_village","poor_family","family","span","backup_filename"},
			new String[]{result.toString(),
				String.valueOf(spend/1000) + "s",
				String.valueOf(Collections.isEmpty(poorVillageCount) ? "0" : poorVillageCount.size()),
				String.valueOf(Collections.isEmpty(poorFamilyCount) ? "0" : poorFamilyCount.size()),
				String.valueOf(Collections.isEmpty(familyCount) ? "0" : familyCount.size()),
				origin.toString()+"-"+derivation.toString(),
				filename}));
		
		//生成正式的备份文件
		finish(filename,false);
		//更新数据库对象，如视图等
		updateDatabase(derivation);
		log.warn("所有数据备份结束，耗时："+ spend +"ms");
		backupProgress.put("progress","100%");
		backupProgress.put("info","备份成功");
		return result;
	}


	/**
	 * 更新数据库对象
	 */
	private void updateDatabase(final int derivation) {
		//跟新视图
		getCurrentSession().doWork(new Work() {
			@Override
			public void execute(Connection con) throws SQLException {
				PreparedStatement ps = con.prepareStatement("DROP VIEW IF EXISTS poorfamily_income");
				ps.execute();
				ps = con.prepareStatement("CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `poorfamily_income` "
					+ " AS select `a`.`ID` AS `areaId`,`a`.`fatherId` AS `areaFatherId`,`pf`.`id` AS `poorFamilyId`,(`pf`.`PERCAPITA` * count(0)) AS `poorFamilyIncome`,count(0) AS `poorFamilyCount` "
					+ " from ((`area` `a` join `poor_family` `pf`) join `family` `f`) "
					+ " where ((`a`.`ID` = `pf`.`AREA_ID`) and (`pf`.`id` = `f`.`FID`) and (`pf`.`IS_DEL` = '0') and (`f`.`IS_DEL` = '0') and  poverty!='已脱贫（不再享受政策）' and poverty <> '防贫户'"
					+ "and (`pf`.`ANNUAL` = '"+ String.valueOf(derivation)+"') and (`f`.`ANNUAL` = '"+ String.valueOf(derivation)+"')) group by `pf`.`id`");
				ps.execute();
			} 
		});
	}

	/**
	 * 按表年度数据备份
	 * @throws IOException 
	 */
	@SuppressWarnings("all")
	private Map<String, Object> tableBackup(Integer origin, Integer derivation,Class cla, String table, String field,String dm) throws IOException {

		String deleteMarker = StringUtils.isNotBlank(dm) ? dm : " IS_DEL='0' ";
		
		Long start = System.currentTimeMillis();
		Map<String, Object> result = new HashMap<>();
		
		//读取历史文件，防止重复备份，同时按addtime字段进行增量备份
		List<Map<String,String>> recordList = readHistoryFile();
		String lastBackupTime = "";
		for (Map<String, String> map : recordList) {
			String[] ss = map.get("backup").split("-");
			if(ss[0].equals(origin.toString()) && ss[1].equals(derivation.toString())) {
				lastBackupTime = map.get("start");
			}
		}

		// 复制原数据为备份数据
		getCurrentSession()
			.createSQLQuery("INSERT INTO " + table + " (" + field + " ANNUAL) SELECT " + field + "concat('derive:',id,'|annual:','"
				+ origin + "') as ANNUAL FROM " + table + " WHERE "+ deleteMarker +" AND ANNUAL='" + origin + "'"
				+ (StringUtils.isNotBlank(lastBackupTime) ? " AND add_time > '" + lastBackupTime +"'" : ""))
			.executeUpdate();

		// 将原数据年度修改为目标年度，作为常用数据
		getCurrentSession()
			.createSQLQuery(" update " + table + " set annual='" + derivation + "' where "+ deleteMarker +" and ANNUAL ='" + origin + "'"
				+ (StringUtils.isNotBlank(lastBackupTime) ? " AND add_time > '" + lastBackupTime + "'" : ""))
			.executeUpdate();

		// 备份后的主键对应map
		List<Map<String, Object>> persistentObject = getCurrentSession()
			.createSQLQuery("select id as id,annual as annual from " + table + " where "+ deleteMarker +" AND ANNUAL LIKE 'derive%'")
			.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();

		Map<Long, Long> associate = new HashMap<>(persistentObject.size(),1F);
		for (Map<String, Object> e : persistentObject) {
			String annual = e.get("annual").toString();
			Long oldId = Long.parseLong(StringUtils.isNotBlank(annual) ? annual.split("\\|")[0].split(":")[1] : "0");
			associate.put(oldId, Long.parseLong(e.get("id").toString()));
		}
		
		result.put("result", BACKUP_SUCCESS);
		result.put("associate", associate);
		result.put("spend", System.currentTimeMillis() - start);
		return result;
	}
	
	/**
	 * 更新备份数据年度字段
	 * @param table
	 * @param origin
	 */
	private void updateANNUAL(String table,Integer origin) {
		// 更新备份数据年度字段
		getCurrentSession()
			.createSQLQuery("UPDATE " + table + " SET ANNUAL='" + origin + "' WHERE ANNUAL LIKE 'derive%'")
			.executeUpdate();
	}

	/**
	 * 获取类字段
	 */
	private Map<String, Object> getFieldInfo(Class cla) {
		Map<String, Object> fieldInfo = new HashMap<>();
		Field fields[] = cla.getDeclaredFields();
		fieldInfo.put("fields", fields);

		List<String> columns = new ArrayList<>();
		for (Field e : fields) {
			Column c = e.getAnnotation(Column.class);
			JoinColumn jc = e.getAnnotation(JoinColumn.class);
			OneToOne oto = e.getAnnotation(OneToOne.class);
			ManyToOne mto = e.getAnnotation(ManyToOne.class);
			OneToMany otm = e.getAnnotation(OneToMany.class);
			if ((c == null && jc == null) || (jc != null && otm != null)) continue;

			columns.add(c != null ? c.name() : jc != null ? jc.name() : "");
		}
		fieldInfo.put("columns", columns);
		return fieldInfo;
	}
	
	/**
	 * 日志记录
	 * @param table
	 * @param origin
	 */
	private void addLog(String ip,HttpSession session, String table , Integer origin , Integer derivation) {
		Log log = new Log();
		log.setAddTime(new Date());
		log.setMethod("备份");
		log.setDescription("年度数据备份（源数据年度："+ origin +" | 目标年度："+ derivation );
		log.setType("系统");
		log.setRequestIp(ip == null ? "" : ip);
		log.setUserName(((McUser)session.getAttribute("user")).getUsername());
		log.setParams("table:"+table+",origin:"+origin+",derivation:"+derivation);
		logService.addlog(log);
	}
	
	/**
	 * 生成历史记录文件
	 * @throws IOException 
	 */
	private void generateHistoryFile(Map<String,String> contentMap) throws IOException {
		log.info("生成历史文件");
		
		File backupFilePath = new File(jdbcSettingBean.getDataPath());
		if(!backupFilePath.exists()) {
			backupFilePath.mkdirs();
		}
		Map<String, String> content = Collections.fill(new LinkedHashMap<String, String>(),
				new String[]{"start", "backup", "backup_filename", "poor_village", "poor_family", "family", "effect", "take"},
				new String[]{
						new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date()),
						contentMap.get("span"),
						contentMap.get("backup_filename"),
						contentMap.get("poor_village"),
						contentMap.get("poor_family"),
						contentMap.get("family"),
						contentMap.get("effect"),
						contentMap.get("spend")});
		
		File file = new File(backupFilePath+"/"+ historyFilename);
		BufferedWriter bw = new BufferedWriter(
			new FileWriter(file,true));
		if(file.length() == 0 ) {
			bw.write("#该文件为数据备份历史文件，数据备份操作将依赖此文件，修改此文件可能会生意外的数据错误");
			bw.newLine();
			bw.flush();
		}
		for(Map.Entry<String,String> entry : content.entrySet()) {
			bw.write(entry.getKey() + " : " + entry.getValue());
			bw.newLine();
			bw.flush();
		}
		bw.write(separator);
		bw.newLine();
		bw.close();
	}
	
	/**
	 * 读取历史文件获取备份信息
	 * @return
	 * @throws IOException
	 */
	@Override
	public List<Map<String,String>> getBackupInfo() throws IOException {
		return readHistoryFile();
	}
	
	/**
	 * 读取历史记录文件
	 * @throws IOException 
	 */
	private List<Map<String,String>> readHistoryFile() throws IOException {
		File backupFilePath = new File(jdbcSettingBean.getDataPath());
		File historyFile =  new File(backupFilePath+"/"+historyFilename);
		List<Map<String,String>> recordList = new ArrayList<>();
		if(historyFile.exists()) {
			BufferedReader br = new BufferedReader(new InputStreamReader(FileUtils.openInputStream(historyFile)));
			
			String line = null;
			Map<String,String> record = new HashMap<>();
			while((line = br.readLine()) != null) {
				if(line.startsWith("#") || StringUtils.isBlank(line)) continue;
				if(!line.startsWith("*")) {
					String[] ss = line.split("\\s:\\s");
					record.put(ss[0].trim(), ss[1].trim());
				} else {
					recordList.add(record);
					record = new HashMap<>();
				}
			}
		}
		
		return recordList;
	}

	/**
	 * 生成永久恢复文件
	 */
	private void generatePermanentFile(HttpSession session ,String filename ) throws IOException {
		File backupFilePath = new File(jdbcSettingBean.getDataPath());
		if(!backupFilePath.exists()) {
			backupFilePath.mkdirs();
		}
		FileUtils.copyInputStreamToFile(Runtime.getRuntime().exec(CMDCommandAll(session)).getInputStream(), new File(backupFilePath+"/"+filename+".sql"));
	}
	/**
	 * 生成恢复文件
	 * @param session
	 * @throws IOException
	 */
	private void generateRestoreFile(HttpSession session, String filename) throws IOException {
		File backupFilePath = new File(jdbcSettingBean.getDataPath());
		if(!backupFilePath.exists()) {
			backupFilePath.mkdirs();
		}
		 FileUtils.copyInputStreamToFile(Runtime.getRuntime().exec(CMDCommand(session)).getInputStream(), new File(backupFilePath+"/"+filename));
	}
	
	/**
	 * 收尾处理
	 * @throws IOException
	 */
	private void finish(String baseFilename,boolean isFailed) {
		File backupFilePath = new File(jdbcSettingBean.getDataPath());
		if(!backupFilePath.exists()) {
			return ;
		}
		
		//判断是否第一次对指定的年度进行备份
		File bakupFile = new File(backupFilePath+"/" + baseFilename + ".backup");
		if(!bakupFile.exists()) {
			File file = new File(backupFilePath+"/" + baseFilename + ".tmp");
			file.renameTo(new File(backupFilePath+"/" + baseFilename + ".backup")); 
			return;
		}
		if(isFailed) {
			File[] files = backupFilePath.listFiles(new FilenameFilter() {
				@Override
				public boolean accept(File arg0, String name) {
					return name.contains(".tmp");
				}
			});
			for (File file : files) {
				file.delete();
			}
			return ;
		} 
		
		bakupFile.delete();
		File file = new File(backupFilePath+"/" + baseFilename + ".tmp");
		file.renameTo(new File(backupFilePath+"/" + baseFilename + ".backup"));
	}
	
	/**
	 * mysql 备份命令(指定表)
	 * @param session
	 * @return
	 * @throws IOException
	 */
	private String CMDCommand(HttpSession session) throws IOException {
		StringBuffer cmd = new StringBuffer();
		cmd.append(jdbcSettingBean.getMysqldump());	// 	
		cmd.append(" -h");						//	-h
		cmd.append(jdbcSettingBean.getHost());			
		cmd.append(" -u");						//	-u
		cmd.append(jdbcSettingBean.getUserName());		
		cmd.append(" -p");						//	-p
		cmd.append(jdbcSettingBean.getPassword());		
		cmd.append(" ");						//	 
		cmd.append(jdbcSettingBean.getDatabaseName());	
		cmd.append(" poor_village poor_family family visit_recd helpunits_and_poorvillage HELP_PERSON_AND_POOR_FAMILY poorfamily_income ");
		return cmd.toString();
	}

	/**
	 * mysql 备份命令(全库)
	 * @param session
	 * @return
	 * @throws IOException
	 */
	private String CMDCommandAll(HttpSession session) throws IOException {
		StringBuffer cmd = new StringBuffer();
		cmd.append(jdbcSettingBean.getMysqldump());	//
		cmd.append(" ");
		cmd.append(jdbcSettingBean.getDatabaseName());
		cmd.append(" -h");						//	-h
		cmd.append(jdbcSettingBean.getHost());
		cmd.append(" -u");						//	-u
		cmd.append(jdbcSettingBean.getUserName());
		cmd.append(" -p");						//	-p
		cmd.append(jdbcSettingBean.getPassword());
		return cmd.toString();
	}
	
}
